﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using SBLib.Common.DbKS;

namespace SBLib.Common.SqlServerKS
{
    public class SqlServerFuncKS
    {
        private string _connectionstring { get; set; }
        public SqlServerFuncKS(string ConnectionString)
        {
            _connectionstring = ConnectionString;
        }

        public List<string> GetServerDatabases()
        {
            try
            {
                DatabaseFuncKS _dbClient = new DatabaseFuncKS(this._connectionstring);
                var retTable = _dbClient.GetDataFromDB("SELECT name FROM sysdatabases ORDER BY name;", "Master", null);

                if (retTable != null)
                {
                    List<string> retBld = new List<string>();
                    foreach (DataRow item in retTable.Rows)
                    {
                        retBld.Add(item["name"].ToString());
                    }

                    return retBld;
                }

                return null;
            }
            catch (Exception)
            {

                return null;
            }
        }

        public List<string> GetDatabaseTables(string prmDatabase)
        {
            try
            {
                DatabaseFuncKS _dbClient = new DatabaseFuncKS(this._connectionstring);
                var retTable = _dbClient.GetDataFromDB("SELECT Table_Name FROM Information_SCHEMA.Tables ORDER BY Table_Name;", prmDatabase, null);

                if (retTable != null)
                {
                    List<string> retBld = new List<string>();
                    foreach (DataRow item in retTable.Rows)
                    {
                        retBld.Add(item["Table_Name"].ToString());
                    }

                    return retBld;
                }

                return null;
            }
            catch (Exception)
            {

                return null;
            }
        }

        public bool IsTableExists(string prmDatabase,string prmTableName)
        {
            try
            {
                DatabaseFuncKS _dbClient = new DatabaseFuncKS(this._connectionstring);
                var retTable = _dbClient.GetDataFromDB(string.Format("SELECT Table_Name FROM Information_SCHEMA.Tables Where Table_Name = '{0}';", prmTableName), prmDatabase, null);

                if (retTable != null)
                {
                    if (retTable.Rows.Count > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                    
                }

                return false;
            }
            catch (Exception)
            {

                return false;
            }
        }

        public List<string> GetTableFields(string prmDatabase,string prmTable)
        {
            try
            {
                DatabaseFuncKS _dbClient = new DatabaseFuncKS(this._connectionstring);
                var retTable = _dbClient.GetDataFromDB(string.Format("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}';", prmTable), prmDatabase, null);

                if (retTable != null)
                {
                    List<string> retBld = new List<string>();
                    foreach (DataRow item in retTable.Rows)
                    {
                        retBld.Add(item["COLUMN_NAME"].ToString());
                    }

                    return retBld;
                }

                return null;
            }
            catch (Exception)
            {

                return null;
            }
        }

//        SELECT COLUMN_NAME, DATA_TYPE, 
//       CHARACTER_MAXIMUM_LENGTH, 
//       NUMERIC_PRECISION, 
//       NUMERIC_SCALE ,
//       (SELECT COLUMNPROPERTY(id ,name, 'ISComputed') AS IsComputed FROM SYSCOLUMNS WHERE Name = ISC.COLUMN_NAME AND  OBJECT_NAME(id) = 'Content' ) AS IsComputed,
//       (SELECT isNullable FROM SYSCOLUMNS WHERE Name = ISC.COLUMN_NAME AND  OBJECT_NAME(id) = 'Content' ) AS isNullable,
//       (SELECT COUNT(COLUMN_NAME) 
//        FROM
//           INFORMATION_SCHEMA.TABLE_CONSTRAINTS ,
//           INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
//        WHERE 
//           INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'PRIMARY KEY' 
//           AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME
//           AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME 
//           AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = 'Content' AND COLUMN_NAME = ISC.COLUMN_NAME) As PrimaryKey,       

//       (SELECT COUNT(COLUMN_NAME) 
//        FROM
//           INFORMATION_SCHEMA.TABLE_CONSTRAINTS ,
//           INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
//        WHERE 
//           INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY' 
//           AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME
//           AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME 
//           AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = 'Content' AND COLUMN_NAME = ISC.COLUMN_NAME) As ForeignKey     
                  
//FROM Information_SCHEMA.columns  ISC
//WHERE TABLE_NAME = 'Content'
//ORDER BY ORDINAL_POSITION



                        
                        
                        

    }
}
